To learn any technology and move to a more advanced level, it is very important to understand the fundamentals of the subject first. Today, we will be talking about something which has been quite introduced a long time ago but not properly explored when it comes to the isolation level.

Snapshot Isolation was introduced in SQL Server in 2005. However, the reality is that there are still many software shops which are using the SQL Server 2000, and therefore cannot be able to maintain the Snapshot Isolation. Many software shops have upgraded to the later version of the SQL Server, but their respective developers have not spend enough time to upgrade themselves with the latest technology. “It works!” is a very common answer of many when they are asked about utilizing the new technology, instead of backward compatibility commands.

In one of the recent consultation project, I had same experience when developers have “heard about it” but have no idea about snapshot isolation. They were thinking it is the same as Snapshot Replication – which is plain wrong. This is the same demo I am including here which I have created for them.

In Snapshot Isolation, the updated row versions for each transaction are maintained in TempDB. Once a transaction has begun, it ignores all the newer rows inserted or updated in the table.

Let us examine this example which shows the simple demonstration. This transaction works on optimistic concurrency model. Since reading a certain transaction does not block writing transaction, it also does not block the reading transaction, which reduced the blocking.

First, enable database to work with Snapshot Isolation. Additionally, check the existing values in the table from HumanResources.Shift.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO

Now, we will need two different sessions to prove this example.

First Session: Set Transaction level isolation to snapshot and begin the transaction. Update the column “ModifiedDate” to today’s date.

-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE HumanResources.Shift
SET ModifiedDate = GETDATE()
GO

Please note that we have not yet been committed to the transaction. Now, open the second session and run the following “SELECT” statement. Then, check the values of the table. Please pay attention on setting the Isolation level for the second one as “Snapshot” at the same time when we already start the transaction using BEGIN TRAN.

-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate
FROM HumanResources.Shift
GO

You will notice that the values in the table are still original values. They have not been modified yet. Once again, go back to session 1 and begin the transaction.

-- Session 1
COMMIT

After that, go back to Session 2 and see the values of the table.

-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO

You will notice that the values are yet not changed and they are still the same old values which were there right in the beginning of the session.

Now, let us commit the transaction in the session 2. Once committed, run the same SELECT statement once more and see what the result is.

-- Session 2
COMMIT
SELECT ModifiedDate
FROM HumanResources.Shift
GO

You will notice that it now reflects the new updated value.

I hope that this example is clear enough as it would give you good idea how the Snapshot Isolation level works. There is much more to write about an extra level, READ_COMMITTED_SNAPSHOT, which we will be discussing in another post soon.

If you wish to use this transaction’s Isolation level in your production database, I would appreciate your comments about their performance on your servers. I have included here the complete script used in this example for your quick reference.

ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE HumanResources.Shift
SET ModifiedDate = GETDATE()
GO
-- Session 2
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 1
COMMIT
-- Session 2
SELECT ModifiedDate
FROM HumanResources.Shift
GO
-- Session 2
COMMIT
SELECT ModifiedDate
FROM HumanResources.Shift
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: Pinal Dave, SQL, SQL Authority, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Transaction Isolation